/*!40101 SET NAMES utf8 */;


-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
DROP PROCEDURE IF EXISTS `sp_simpleSelect`;
DELIMITER $$
CREATE PROCEDURE `sp_simpleSelect` (
    in table1 nvarchar(200),    /* primary table */
    in table2 nvarchar(200),    /* second table */
    in field1 nvarchar(200),    /* primary table primary key */
    in field2 nvarchar(200)    /* second table forgein key */
)
BEGIN
    
    declare currentSchema nvarchar(50);
    declare AlterColumnSql nvarchar(2000);
    declare UpdateSql nvarchar(2000);
    declare ColumType nvarchar(50);
    declare FK_Symbol nvarchar(300);
    declare FK_ExecSql nvarchar(2000);
    declare FK_Exist int default 0;

    ## @@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    ## @@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    ## @@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    ## @@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    ## @@@@@@@@@@@@@@@@@@@@@@@@@@@@@
    ## Change currentSchema into your target schema before run the sql script
    set @currentSchema='card_test';

    /* allow colum null */
    select column_type into @ColumType from information_schema.columns where table_name = table1 and column_name = field1 and table_schema=@currentSchema;
    
    ##select @ColumType; Tow cloumn must in the same type , so we choose the primary table's column
    
    set @AlterColumnSql = CONCAT('alter table ',table2); 
    set @AlterColumnSql = CONCAT(@AlterColumnSql,' modify column ');
    set @AlterColumnSql = CONCAT(@AlterColumnSql,field2);
    set @AlterColumnSql = CONCAT(@AlterColumnSql,' ');
    set @AlterColumnSql = CONCAT(@AlterColumnSql,@ColumType);
    set @AlterColumnSql = CONCAT(@AlterColumnSql,' null');

    ## alter table table2 modify column field2 @ColumType null
    prepare AlterColumnSqlExec from @AlterColumnSql;
    execute AlterColumnSqlExec;
    DEALLOCATE PREPARE AlterColumnSqlExec;

    
    /* update dirty date into null */
    set @UpdateSql = CONCAT('update ',table2);
    set @UpdateSql = CONCAT(@UpdateSql,' set ');
    set @UpdateSql = CONCAT(@UpdateSql,field2);
    set @UpdateSql = CONCAT(@UpdateSql,' = null where ');
    set @UpdateSql = CONCAT(@UpdateSql,field2);
    set @UpdateSql = CONCAT(@UpdateSql,' not in( select ');
    set @UpdateSql = CONCAT(@UpdateSql,field1);
    set @UpdateSql = CONCAT(@UpdateSql,' from ');
    set @UpdateSql = CONCAT(@UpdateSql,table1);
    set @UpdateSql = CONCAT(@UpdateSql,' )');
    
    ## 'update table2 set field2 = null where field2 not in( select field1 from table1)'
    prepare UpdateSqlExec from @UpdateSql;
    execute UpdateSqlExec;
    DEALLOCATE PREPARE UpdateSqlExec;
    
    
    ##select @AlterColumnSql;
    ##select @UpdateSql;
    
    
    /* add FK */
    ## @FK_Symbol = FK_table2_table1_field2_field1
    set @FK_Symbol = CONCAT('FK_',table2);
    set @FK_Symbol = CONCAT(@FK_Symbol  , '_');
    set @FK_Symbol = CONCAT(@FK_Symbol  , table1);
    set @FK_Symbol = CONCAT(@FK_Symbol  , '_');
    set @FK_Symbol = CONCAT(@FK_Symbol  , field2);
    set @FK_Symbol = CONCAT(@FK_Symbol  , '_');
    set @FK_Symbol = CONCAT(@FK_Symbol  , field1); 
    
    ## InnoDB engine support FK    
    ## ALTER TABLE table2 ENGINE = InnoDB ;
    ## ALTER TABLE table1 ENGINE = InnoDB ;
    set @FK_ExecSql = CONCAT('alter table ',table2);
    set @FK_ExecSql = CONCAT(@FK_ExecSql, ' ENGINE = InnoDB');
    prepare FKSqlExec from @FK_ExecSql;
    execute FKSqlExec;
    DEALLOCATE PREPARE FKSqlExec;
    set @FK_ExecSql = CONCAT('alter table ',table1);
    set @FK_ExecSql = CONCAT(@FK_ExecSql, ' ENGINE = InnoDB');
    prepare FKSqlExec from @FK_ExecSql;
    execute FKSqlExec;
    DEALLOCATE PREPARE FKSqlExec;

    select count(*) into @FK_Exist 
        from information_schema.KEY_COLUMN_USAGE  
        where table_name = table2 and constraint_name = @FK_Symbol and constraint_schema=@currentSchema;
    if(@FK_Exist > 0) then
        set @FK_ExecSql = CONCAT('alter table ',table2);
        set @FK_ExecSql = CONCAT(@FK_ExecSql, ' drop FOREIGN KEY ');
        set @FK_ExecSql = CONCAT(@FK_ExecSql, @FK_Symbol);
        ## alter table table2 drop FOREIGN KEY @FK_Symbol;
        prepare FKSqlExec from @FK_ExecSql;
        execute FKSqlExec;
        DEALLOCATE PREPARE FKSqlExec;
    end if;
    
    ## select 1;
    
    set @FK_ExecSql = CONCAT('alter table ',table2);
    set @FK_ExecSql = CONCAT(@FK_ExecSql, ' ADD CONSTRAINT ');
    set @FK_ExecSql = CONCAT(@FK_ExecSql, @FK_Symbol);
    set @FK_ExecSql = CONCAT(@FK_ExecSql, ' FOREIGN KEY( ');
    set @FK_ExecSql = CONCAT(@FK_ExecSql, field2);
    set @FK_ExecSql = CONCAT(@FK_ExecSql, ' ) references ');
    set @FK_ExecSql = CONCAT(@FK_ExecSql, table1);
    set @FK_ExecSql = CONCAT(@FK_ExecSql, '( ');
    set @FK_ExecSql = CONCAT(@FK_ExecSql, field1);
    set @FK_ExecSql = CONCAT(@FK_ExecSql, ' )');
    ## alter table table2 ADD CONSTRAINT @FK_Symbol FOREIGN KEY( field2 ) references table1( field1 );
    prepare FKSqlExec from @FK_ExecSql;
    execute FKSqlExec;
    DEALLOCATE PREPARE FKSqlExec;
    
    ## select 2;
    
END$$
DELIMITER ;


## create table
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
CREATE  TABLE IF NOT EXISTS exchange(
  id INT NOT NULL AUTO_INCREMENT ,
  currency_short_name VARCHAR(45) NOT NULL ,
  currency_full_name VARCHAR(60) NULL ,
  rate_target VARCHAR(45) NOT NULL DEFAULT '\"CNY\"' ,
  rate DOUBLE NOT NULL ,
  PRIMARY KEY (id) );
ALTER TABLE exchange CHARACTER SET = utf8 , COLLATE = utf8_bin;
ALTER TABLE exchange CHANGE COLUMN currency_full_name currency_full_name VARCHAR(60) CHARACTER SET 'utf8' COLLATE 'utf8_bin' NULL DEFAULT NULL;


CREATE  TABLE IF NOT EXISTS favorites(
  favoriteid INT NOT NULL AUTO_INCREMENT ,
  customer_id INT(11) NOT NULL ,
  card_id INT(6) NOT NULL ,
  disabled TINYINT(1) NOT NULL DEFAULT 0 ,
  create_date DATETIME NULL ,
  last_modify_date DATETIME NULL ,
  PRIMARY KEY (favoriteid) )
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;


CREATE  TABLE `sys_verify` (
  `vcode` VARCHAR(36) NOT NULL COMMENT 'GUID认证码，只应该使用一次，使用完毕后应该删除本记录。' ,
  `for_id` INT NOT NULL COMMENT '指向某张表的某个主键，各种服务各自业务逻辑控制其行为。' ,
  `expire_after` INT NOT NULL DEFAULT 3600 COMMENT '多少秒之后此行数据失效，默认值为3600（一小时）。各种服务失效时间各异，但是所有数据由统一引擎回收。' ,
  `data` VARCHAR(135) NULL COMMENT '任何相关的额外信息。' ,
  PRIMARY KEY (`vcode`) )
COMMENT = '存储临时认证码，为系统各类功能服务。'
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;


CREATE  TABLE `sys_log` (
  `log_id` INT UNSIGNED NOT NULL AUTO_INCREMENT ,
  `created_date` DATETIME NOT NULL ,
  `verb` TINYINT UNSIGNED NOT NULL COMMENT '0 -- system, 1 -- business' ,
  `module` VARCHAR(45) NULL ,
  `message` VARCHAR(225) NOT NULL ,
  `data` VARCHAR(450) NULL ,
  PRIMARY KEY (`log_id`) )
COMMENT = '系统日志'
ENGINE = InnoDB
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_bin;


## alter table
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
ALTER TABLE card_infor ADD COLUMN is_sold_hot_top SMALLINT(1) NULL  AFTER order_value;
ALTER TABLE game ADD COLUMN game_photo VARCHAR(45) NULL  AFTER isshow;
ALTER TABLE card ADD COLUMN is_sold_promotion SMALLINT(1) NULL  AFTER soldlastmonth;
ALTER TABLE game ADD COLUMN is_hot_top SMALLINT(1) NULL  AFTER game_photo;
ALTER TABLE card_class ADD COLUMN category_key VARCHAR(20) NULL;
ALTER TABLE game ADD COLUMN game_key VARCHAR(50) NULL;
ALTER TABLE card_infor ADD COLUMN card_key VARCHAR(50) NULL;
ALTER TABLE card ADD COLUMN card_key VARCHAR(50) NULL;
ALTER TABLE customer CHANGE COLUMN isactive isactive TINYINT(4) NOT NULL DEFAULT '0';
ALTER TABLE orders CHANGE COLUMN process_time process_time DATETIME NULL DEFAULT NULL  , CHANGE COLUMN order_time order_time DATETIME NULL DEFAULT NULL;
ALTER TABLE customer ADD COLUMN birthday DATE NULL  AFTER very_code;
ALTER TABLE `customer` CHANGE COLUMN `reg_time` `reg_time` DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00'  , CHANGE COLUMN `lastlogin_time` `lastlogin_time` DATETIME NOT NULL DEFAULT '2000-01-01 00:00:00'  ;
ALTER TABLE `customer` ADD COLUMN `recommended` INT(11) NULL DEFAULT NULL  AFTER `birthday` , 
  ADD CONSTRAINT `recommended_cust_id`
  FOREIGN KEY (`recommended` )
  REFERENCES `customer` (`cust_id` )
  ON DELETE NO ACTION
  ON UPDATE NO ACTION
, ADD INDEX `recommended_cust_id` (`recommended` ASC) ;
ALTER TABLE `customer` ADD COLUMN `email_confirmed` BIT NOT NULL DEFAULT 0  AFTER `email` ;


## example ##
   ## in table1 nvarchar(200),    /* primary table */
   ## in table2 nvarchar(200),    /* second table */
   ## in field1 nvarchar(200),    /* primary table primary key */
   ## in field2 nvarchar(200)    /* second table forgein key */
## call sp_simpleSelect('card_infor','card','card_info_id','card_infor_id')

## FK Update
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
call sp_simpleSelect('card_infor','card','card_info_id','card_infor_id');
call sp_simpleSelect('card_class','card_infor','class_id','card_class');
call sp_simpleSelect('card_infor','card_game','card_info_id','card_infor_id');
call sp_simpleSelect('game','card_game','game_id','game_id');
call sp_simpleSelect('card_producer','card_infor','producer_id','producer_id');
call sp_simpleSelect('card','charge_option','card_id','card_id');

call sp_simpleSelect('orders','orders_detail','orders_id','orders_id');
call sp_simpleSelect('card','orders_detail','card_id','card_id');
call sp_simpleSelect('orders','charge_detail','orders_id','orders_id');
call sp_simpleSelect('orders_detail','charge_detail','orders_detail_id','order_detail_id');
call sp_simpleSelect('card','orders','card_id','card_1');
call sp_simpleSelect('customer','orders','cust_id','cust_id');

call sp_simpleSelect('customer','favorites','cust_id','customer_id');
call sp_simpleSelect('card','favorites','card_id','card_id');


## data init
-- --------------------------------------------------------------------------------
-- Routine DDL
-- Note: comments before and after the routine body will not be stored by the server
-- --------------------------------------------------------------------------------
update game set game_photo = 'chuanyuehuoxian.jpg' where game_id = 310;
update card_infor set is_sold_hot_top = 1 where onsale = 1 and hot = 1;
update game set is_hot_top = 1 where isshow = 1 and ishot = 1;
update card set is_sold_promotion = 1 where card_id = 356;
update card set is_sold_promotion = 1 where card_id = 260;

insert into  exchange (`currency_short_name`, `currency_full_name`, `rate_target`, `rate`) values ('USD','美元','CNY',6.64);
insert into  exchange (`currency_short_name`, `currency_full_name`, `rate_target`, `rate`) values ('CAD','加元','CNY',6.6);
insert into  exchange (`currency_short_name`, `currency_full_name`, `rate_target`, `rate`) values ('GBP','英镑','CNY',10.7);
insert into  exchange (`currency_short_name`, `currency_full_name`, `rate_target`, `rate`) values ('EUR','欧元','CNY',9.4);
insert into  exchange (`currency_short_name`, `currency_full_name`, `rate_target`, `rate`) values ('AUD','澳元','CNY',6.7);
insert into  exchange (`currency_short_name`, `currency_full_name`, `rate_target`, `rate`) values ('JPY','日元','CNY',0.081);
insert into  exchange (`currency_short_name`, `currency_full_name`, `rate_target`, `rate`) values ('SGD','新元','CNY',5.1);
insert into  exchange (`currency_short_name`, `currency_full_name`, `rate_target`, `rate`) values ('CNY','人民币','CNY',1);

## test data
insert into customer values(1,'admin', old_password('User@123'), '管理员', ' ', '美国(001)test2', '0', '-1', '0', 'test1@163.com', 1, 'test1', '0', '中国', '', '', '中国(0086)test1', '', 'http://localhost/CardInChina.Frontend/index.asp', '::1', '', 'fdxhcyfkbbvocnta', '0', '0', '500', '500', '0.00', '0', '0', '0', '0', ' ', ' ', '2011-11-01 23:14:17', '2011-11-01 23:14:17', 'c3f6cfe2af11e5f3e47764a01286f956', NULL, NULL);
insert into customer values(2,'stainboy', old_password('User@123'), 'Miles Chen', ' ', '美国(001)test2', '0', '-1', '0', 'test1@163.com', 1, 'test1', '0', '中国', '', '', '中国(0086)test1', '', 'http://localhost/CardInChina.Frontend/index.asp', '::1', '', 'fdxhcyfkbbvocnta', '0', '0', '500', '500', '0.00', '0', '0', '0', '0', ' ', ' ', '2011-11-01 23:14:17', '2011-11-01 23:14:17', 'c3f6cfe2af11e5f3e47764a01286f956', NULL, NULL);
insert into customer values(3,'yoyo', old_password('User@123'), '', ' ', '美国(001)test2', '0', '-1', '0', 'test1@163.com', 0, 'test1', '0', '中国', '', '', '中国(0086)test1', '', 'http://localhost/CardInChina.Frontend/index.asp', '::1', '', 'fdxhcyfkbbvocnta', '0', '0', '500', '500', '0.00', '0', '0', '0', '0', ' ', ' ', '2011-11-01 23:14:17', '2011-11-01 23:14:17', 'c3f6cfe2af11e5f3e47764a01286f956', NULL, NULL);
insert into customer values(4,'molly', old_password('User@123'), '囡囡', ' ', '美国(001)test2', '0', '-1', '0', 'test1@163.com', 0, 'test1', '0', '中国', '', '', '中国(0086)test1', '', 'http://localhost/CardInChina.Frontend/index.asp', '::1', '', 'fdxhcyfkbbvocnta', '0', '0', '500', '500', '0.00', '0', '0', '0', '0', ' ', ' ', '2011-11-01 23:14:17', '2011-11-01 23:14:17', 'c3f6cfe2af11e5f3e47764a01286f956', NULL, NULL);


update card_class set category_key = 'game' where class_name = '游戏卡';
update card_class set category_key = '5173' where class_name = '游戏币';
update card_class set category_key = 'qq' where class_name = 'QQ系列';
update card_class set category_key = 'secure' where class_name = '防病毒/代理';
update card_class set category_key = 'hk-tw-game' where class_name = '台服游戏卡';
update card_class set category_key = 'mobile' where class_name = '神州行卡';

update card_infor set card_key = uuid();
update game set game_key = uuid();
update card set card_key = uuid();

## resolve Unable to convert MySQL date/time value to System.DateTime
update orders set process_time = NULL where process_time = '0000-00-00 00:00:00';
update customer set reg_time = '2000-01-01 00:00:00' where reg_time = '0000-00-00 00:00:00';
update customer set lastlogin_time = '2000-01-01 00:00:00' where lastlogin_time = '0000-00-00 00:00:00';



## cleanup
DROP PROCEDURE IF EXISTS `sp_simpleSelect`;